﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.StartPanel;
using Lima.ResumeSys.Class;
using Lima.ResumeSys.Models;
using System.Data.OleDb;
using System.Drawing.Printing;
using System.Collections;
using Lima.ResumeSys.Util;

namespace Lima.ResumeSys.DAL
{
    public class PerSonInfoDAL
    {
        private clsDbOperate dbOperate = new clsDbOperate();

        public int GetResultCount(string perIdCard)
        {
            string sql = @"select * from PersonInfo where PerIdCard='@perIdCard'";
            OleDbParameter[] parameters = {
            new OleDbParameter("@perIdCard", perIdCard) };
            DataTable dt = dbOperate.ExecuteQuery(sql, parameters);

            return dt.Rows.Count; ;
        }

        public PersonInfo ConvertToModel(DataRow row)
        {
            PersonInfo person = new PersonInfo();

            if (!string.IsNullOrEmpty(row["PerNo"].ToString()))
            {
                person.PerNo = row["PerNo"].ToString();
            }
            if (!string.IsNullOrEmpty(row["PerName"].ToString()))
            {
                person.PerName = row["PerName"].ToString();
            }
            if (!string.IsNullOrEmpty(row["PerSex"].ToString()))
            {
                person.PerSex = row["PerSex"].ToString();
            }
            if (!string.IsNullOrEmpty(row["PerBirth"].ToString()))
            {
                person.PerBirth = Convert.ToDateTime(row["PerBirth"].ToString());
            }
            if (!string.IsNullOrEmpty(row["PerFormerName"].ToString()))
            {
                person.PerFormerName = row["PerFormerName"].ToString();
            }
            if (!string.IsNullOrEmpty(row["PerNationality"].ToString()))
            {
                person.PerNationality = row["PerNationality"].ToString();
            }
            if (!string.IsNullOrEmpty(row["PerIdCard"].ToString()))
            {
                person.PerIdCard = row["PerIdCard"].ToString();
            }
            if (!string.IsNullOrEmpty(row["PerNativePlace"].ToString()))
            {
                person.PerNativePlace = row["PerNativePlace"].ToString();
            }
            if (!string.IsNullOrEmpty(row["PerMaritalStatus"].ToString()))
            {
                person.PerMaritalStatus = row["PerMaritalStatus"].ToString();
            }
            if (!string.IsNullOrEmpty(row["PerEducationLevel"].ToString()))
            {
                person.PerEducationLevel = row["PerEducationLevel"].ToString();
            }
            if (!string.IsNullOrEmpty(row["PerPoliticalStatus"].ToString()))
            {
                person.PerPoliticalStatus = row["PerPoliticalStatus"].ToString();
            }
            if (!string.IsNullOrEmpty(row["PerJoinPartyTime"].ToString()))
            {
                person.PerJoinPartyTime = Convert.ToDateTime(row["PerJoinPartyTime"].ToString());
            }
            if (!string.IsNullOrEmpty(row["PerWorkStartTime"].ToString()))
            {
                person.PerWorkStartTime = Convert.ToDateTime(row["PerWorkStartTime"].ToString());
            }
            if (!string.IsNullOrEmpty(row["PerOrgId"].ToString()))
            {
                person.PerOrgId = row["PerOrgId"].ToString();
            }
            if (!string.IsNullOrEmpty(row["PerOrgName"].ToString()))
            {
                person.PerOrgName = row["PerOrgName"].ToString();
            }
            if (!string.IsNullOrEmpty(row["PerUnitNature"].ToString()))
            {
                person.PerUnitNature = row["PerUnitNature"].ToString();
            }
            if (!string.IsNullOrEmpty(row["PerJobPosition"].ToString()))
            {
                person.PerJobPosition = row["PerJobPosition"].ToString();
            }
            if (!string.IsNullOrEmpty(row["PerJobLevel"].ToString()))
            {
                person.PerJobLevel = row["PerJobLevel"].ToString();
            }
            if (!string.IsNullOrEmpty(row["PerJobTitle"].ToString()))
            {
                person.PerJobTitle = row["PerJobTitle"].ToString();
            }
            if (!string.IsNullOrEmpty(row["PerManagementWork"].ToString()))
            {
                person.PerManagementWork = row["PerManagementWork"].ToString();
            }
            if (!string.IsNullOrEmpty(row["PerTenureTime"].ToString()))
            {
                person.PerTenureTime = Convert.ToDateTime(row["PerTenureTime"].ToString());
            }
            if (!string.IsNullOrEmpty(row["PerAddress"].ToString()))
            {
                person.PerAddress = row["PerAddress"].ToString();
            }
            if (!string.IsNullOrEmpty(row["PerPhoto"].ToString()))
            {
                person.PerPhoto = row["PerPhoto"].ToString();
            }
            if (!string.IsNullOrEmpty(row["Creator"].ToString()))
            {
                person.Creator = row["Creator"].ToString();
            }
            if (!string.IsNullOrEmpty(row["CreateTime"].ToString()))
            {
                person.CreateTime = Convert.ToDateTime(row["CreateTime"].ToString());
            }
            if (!string.IsNullOrEmpty(row["UpdatedBy"].ToString()))
            {
                person.UpdatedBy = row["UpdatedBy"].ToString();
            }
            if (!string.IsNullOrEmpty(row["UpdateTime"].ToString()))
            {
                person.UpdateTime = Convert.ToDateTime(row["UpdateTime"].ToString());
            }
            return person;
        }

        public bool Del(string perNo)
        {
            try
            {
                string sql = @"delete from PersonInfo where PerNo=@perNo ";
                OleDbParameter[] parameters = { new OleDbParameter("@perNo", perNo) };
                int result = dbOperate.ExecuteNonQuery(sql, parameters);
                return result > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public PersonInfo GetModel(string perNo)
        {
            try
            {
                string sql = @"select * from PersonInfo where PerNo=@perNo ";
                OleDbParameter[] parameters = { new OleDbParameter("@perNo", perNo) };
                DataTable dt = dbOperate.ExecuteQuery(sql, parameters);
                if (dt != null && dt.Rows.Count > 0)
                {
                    DataRow row = dt.Rows[0];
                    return ConvertToModel(row);
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw;
            }

        }

        public bool DelPerSonInfo(string perNo)
        {
            string[] tablesToDelete = { "PersonInfo","PerFile", "RewardPunish" };
            OleDbParameter[] parameters;
            int totalDeleted = 0;
            string sql;
            try
            {
                foreach (string table in tablesToDelete)
                {
                    sql = $"DELETE FROM {table} WHERE PerNo=@perNo ";
                    parameters = new OleDbParameter[] { new OleDbParameter("@perNo", perNo) };
                    totalDeleted += dbOperate.ExecuteNonQuery(sql, parameters);
                }
                return totalDeleted > 0;
            }
            catch (Exception)
            {
                return false;
            }

        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Add(PersonInfo model)
        {
            try
            {
                string sql = @"INSERT INTO PersonInfo(PerNo, PerName, PerSex, PerBirth, PerFormerName, PerPhoto,PerNationality, PerIdCard, PerNativePlace, PerMaritalStatus,  PerEducationLevel,PerPoliticalStatus, PerJoinPartyTime, PerWorkStartTime, PerOrgId, PerOrgName, PerUnitNature, PerJobPosition,  PerJobLevel, PerJobTitle, PerManagementWork,PerTenureTime,PerAddress,Creator,CreateTime,UpdatedBy, UpdateTime)
              VALUES(@PerNo,@PerName,@PerSex,@PerBirth,@PerFormerName,@PerPhoto,@PerNationality,@PerIdCard,@PerNativePlace,@PerMaritalStatus, 
                @PerEducationLevel,@PerPoliticalStatus,@PerJoinPartyTime,@PerWorkStartTime, @PerOrgId,@PerOrgName, @PerUnitNature, @PerJobPosition,@PerJobLevel, @PerJobTitle,@PerManagementWork,@PerTenureTime,@PerAddress,@Creator,@CreateTime,@UpdatedBy,@UpdateTime)";

                List<OleDbParameter> parameters = new List<OleDbParameter>();
                parameters.Add(new OleDbParameter("@PerNo", model.PerNo));
                parameters.Add(new OleDbParameter("@PerName", model.PerName));
                parameters.Add(new OleDbParameter("@PerSex", model.PerSex));

                if (!string.IsNullOrWhiteSpace(model.PerBirth.ToString()))
                {
                    parameters.Add(new OleDbParameter("@PerBirth", model.PerBirth.ToString()));
                }
                else
                {
                    var par = new OleDbParameter("@PerBirth", OleDbType.Date);
                    par.Value = DBNull.Value;
                    parameters.Add(par);
                }
                parameters.Add(new OleDbParameter("@PerFormerName", model.PerFormerName));
                parameters.Add(new OleDbParameter("@PerPhoto", model.PerPhoto));
                parameters.Add(new OleDbParameter("@PerNationality", model.PerNationality));
                parameters.Add(new OleDbParameter("@PerIdCard", model.PerIdCard));
                parameters.Add(new OleDbParameter("@PerNativePlace", model.PerNativePlace));
                parameters.Add(new OleDbParameter("@PerMaritalStatus", model.PerMaritalStatus));
                parameters.Add(new OleDbParameter("@PerEducationLevel", model.PerEducationLevel));
                parameters.Add(new OleDbParameter("@PerPoliticalStatus", model.PerPoliticalStatus));

                if (!string.IsNullOrWhiteSpace(model.PerJoinPartyTime.ToString()))
                {
                    parameters.Add(new OleDbParameter("@PerJoinPartyTime", model.PerJoinPartyTime.ToString()));
                }
                else
                {
                    var par = new OleDbParameter("@PerJoinPartyTime", OleDbType.Date);
                    par.Value = DBNull.Value;
                    parameters.Add(par);
                }
                if (!string.IsNullOrWhiteSpace(model.PerWorkStartTime.ToString()))
                {
                    parameters.Add(new OleDbParameter("@PerWorkStartTime", model.PerWorkStartTime.ToString()));
                }
                else
                {
                    var par = new OleDbParameter("@PerWorkStartTime", OleDbType.Date);
                    par.Value = DBNull.Value;
                    parameters.Add(par);
                }
                parameters.Add(new OleDbParameter("@PerOrgId", model.PerOrgId));
                parameters.Add(new OleDbParameter("@PerOrgName", model.PerOrgName));
                parameters.Add(new OleDbParameter("@PerUnitNature", model.PerUnitNature));
                parameters.Add(new OleDbParameter("@PerJobPosition", model.PerJobPosition));
                parameters.Add(new OleDbParameter("@PerJobLevel", model.PerJobLevel));
                parameters.Add(new OleDbParameter("@PerJobTitle", model.PerJobTitle));
                parameters.Add(new OleDbParameter("@PerManagementWork", model.PerManagementWork));
                if (!string.IsNullOrWhiteSpace(model.PerTenureTime.ToString()))
                {
                    parameters.Add(new OleDbParameter("@PerTenureTime", model.PerTenureTime.ToString()));
                }
                else
                {
                    var par = new OleDbParameter("@PerTenureTime", OleDbType.Date);
                    par.Value = DBNull.Value;
                    parameters.Add(par);
                }
                parameters.Add(new OleDbParameter("@PerAddress", model.PerAddress));
                parameters.Add(new OleDbParameter("@Creator", model.Creator));
                parameters.Add(new OleDbParameter("@CreateTime", DateTime.Now.ToString()));
                parameters.Add(new OleDbParameter("@UpdatedBy", model.UpdatedBy));
                parameters.Add(new OleDbParameter("@UpdateTime", DateTime.Now.ToString()));

                var num = dbOperate.ExecuteNonQuery(sql.ToString(), parameters.ToArray());
                return num > 0;
            }
            catch (Exception ex)
            {
                throw new Exception("增加人员信息发生异常：" + ex.Message);
            }
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Update(PersonInfo model)
        {
            try
            {
                string sql = @"UPDATE PersonInfo set  PerName=@PerName, PerSex=@PerSex, PerBirth=@PerBirth, PerFormerName=@PerFormerName, PerPhoto=@PerPhoto,PerNationality=@PerNationality, PerIdCard=@PerIdCard, PerNativePlace=@PerNativePlace, PerMaritalStatus=@PerMaritalStatus,  PerEducationLevel=@PerEducationLevel,PerPoliticalStatus=@PerPoliticalStatus, PerJoinPartyTime=@PerJoinPartyTime, PerWorkStartTime=@PerWorkStartTime, PerOrgId=@PerOrgId, PerOrgName=@PerOrgName, PerUnitNature=@PerUnitNature, PerJobPosition=@PerJobPosition,  PerJobLevel=@PerJobLevel, PerJobTitle=@PerJobTitle, PerManagementWork=@PerManagementWork,PerTenureTime=@PerTenureTime,PerAddress=@PerAddress,UpdatedBy=@UpdatedBy, UpdateTime=@UpdateTime WHERE PerNo=@PerNo";

                List<OleDbParameter> parameters = new List<OleDbParameter>();

                parameters.Add(new OleDbParameter("@PerName", model.PerName));
                parameters.Add(new OleDbParameter("@PerSex", model.PerSex));

                if (!string.IsNullOrWhiteSpace(model.PerBirth.ToString()))
                {
                    parameters.Add(new OleDbParameter("@PerBirth", model.PerBirth.ToString()));
                }
                else
                {
                    var par = new OleDbParameter("@PerBirth", OleDbType.Date);
                    par.Value = DBNull.Value;
                    parameters.Add(par);
                }
                parameters.Add(new OleDbParameter("@PerFormerName", model.PerFormerName));
                parameters.Add(new OleDbParameter("@PerPhoto", model.PerPhoto));
                parameters.Add(new OleDbParameter("@PerNationality", model.PerNationality));
                parameters.Add(new OleDbParameter("@PerIdCard",model.PerIdCard));
                parameters.Add(new OleDbParameter("@PerNativePlace", model.PerNativePlace));
                parameters.Add(new OleDbParameter("@PerMaritalStatus", model.PerMaritalStatus));
                parameters.Add(new OleDbParameter("@PerEducationLevel", model.PerEducationLevel));
                parameters.Add(new OleDbParameter("@PerPoliticalStatus", model.PerPoliticalStatus));

                if (!string.IsNullOrWhiteSpace(model.PerJoinPartyTime.ToString()))
                {
                    parameters.Add(new OleDbParameter("@PerJoinPartyTime", model.PerJoinPartyTime.ToString()));
                }
                else
                {
                    var par = new OleDbParameter("@PerJoinPartyTime", OleDbType.Date);
                    par.Value = DBNull.Value;
                    parameters.Add(par);
                }
                if (!string.IsNullOrWhiteSpace(model.PerWorkStartTime.ToString()))
                {
                    parameters.Add(new OleDbParameter("@PerWorkStartTime", model.PerWorkStartTime.ToString()));
                }
                else
                {
                    var par = new OleDbParameter("@PerWorkStartTime", OleDbType.Date);
                    par.Value = DBNull.Value;
                    parameters.Add(par);
                }
                parameters.Add(new OleDbParameter("@PerOrgId", model.PerOrgId));
                parameters.Add(new OleDbParameter("@PerOrgName", model.PerOrgName));
                parameters.Add(new OleDbParameter("@PerUnitNature", model.PerUnitNature));
                parameters.Add(new OleDbParameter("@PerJobPosition", model.PerJobPosition));
                parameters.Add(new OleDbParameter("@PerJobLevel", model.PerJobLevel));
                parameters.Add(new OleDbParameter("@PerJobTitle", model.PerJobTitle));
                parameters.Add(new OleDbParameter("@PerManagementWork", model.PerManagementWork));
                if (!string.IsNullOrWhiteSpace(model.PerTenureTime.ToString()))
                {
                    parameters.Add(new OleDbParameter("@PerTenureTime", model.PerTenureTime.ToString()));
                }
                else
                {
                    var par = new OleDbParameter("@PerTenureTime", OleDbType.Date);
                    par.Value = DBNull.Value;
                    parameters.Add(par);
                }
                parameters.Add(new OleDbParameter("@PerAddress", model.PerAddress));

                parameters.Add(new OleDbParameter("@UpdatedBy", model.UpdatedBy));
                parameters.Add(new OleDbParameter("@UpdateTime", DateTime.Now.ToString()));
                parameters.Add(new OleDbParameter("@PerNo", model.PerNo));

                dbOperate.ExecuteNonQuery(sql.ToString(), parameters.ToArray());
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("修改人员信息发生异常：" + ex.Message);
            }
        }

        public PersonPageResult QueryPage(QueryPersonInfoModel query)
        {
            PersonPageResult result = new PersonPageResult();

            string strWhere = "";
            List<OleDbParameter> parameters = new List<OleDbParameter>();

            if (!string.IsNullOrWhiteSpace(query.PerName))
            {
                strWhere += " and PerName like @PerName ";
                parameters.Add(new OleDbParameter("@PerName", "%" + query.PerName + "%"));
            }
            if (!string.IsNullOrWhiteSpace(query.PerIdCard))
            {
                strWhere += " and PerIdCard like @PerIdCard ";
                parameters.Add(new OleDbParameter("@PerIdCard", "%" + query.PerIdCard + "%"));
            }
            if (!string.IsNullOrWhiteSpace(query.PerOrgId))
            {
                strWhere += " and PerOrgId = @perOrgId ";
                parameters.Add(new OleDbParameter("@perOrgId", query.PerOrgId));
            }
            string sql = "";
            if (query.CurrentPage <= 1)
            {
                sql = $"SELECT TOP {query.PageSize} * FROM PersonInfo where 1=1 {strWhere} ORDER BY CreateTime DESC";
            }
            else
            {
                int skip = (query.CurrentPage - 1) * query.PageSize;

                sql = $@" SELECT TOP {query.PageSize} * FROM PersonInfo WHERE PerNo NOT IN (SELECT TOP {skip} PerNo FROM PersonInfo where 1=1 {strWhere} ORDER BY CreateTime DESC )
                ORDER BY CreateTime DESC";
            }

            string sqlCount = "select count(*) from PersonInfo where 1=1 " + strWhere;

            //获取总数
            var count = dbOperate.ExecuteScalar(sqlCount, parameters.ToArray());

            //获取数据列表
            var dataTables = dbOperate.ExecuteQuery(sql, parameters.ToArray());

            List<PersonInfo> personInfos = new List<PersonInfo>();
            if (dataTables != null && dataTables.Rows.Count > 0)
            {
                foreach (DataRow row in dataTables.Rows)
                {
                    personInfos.Add(ConvertToModel(row));
                }
            }
            result.Data = personInfos;
            result.PageSize = query.PageSize;
            result.CurrentPage = query.CurrentPage;
            result.Total = Convert.ToInt32(count);
            return result;
        }


    }
}
